package org.apache.poi.ext;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Base64;
import java.util.Date;
import java.util.Locale;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.StringUtils;

public class ExcelTool {

	private static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";

	public static String getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		String cellvalue = null;
		if (cell.getCellType().equals(CellType.STRING)) {
			cellvalue = cell.getStringCellValue();
		} else if (cell.getCellType().equals(CellType.NUMERIC)) {
			// 日期格式
			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
				DataFormatter formatter = new DataFormatter();
				cellvalue = formatter.formatCellValue(cell);
			} else {
				Double value = cell.getNumericCellValue();
				if (value - value.intValue() == 0) {
					cellvalue = String.valueOf(value.intValue());
				} else {
					// 防止数字变成科学计数法的形式
					DecimalFormat df = new DecimalFormat("0.00");
					cellvalue = df.format(value);
				}
			}

		} else if (cell.getCellType().equals(CellType.BOOLEAN)) {
			cellvalue = String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType().equals(CellType.FORMULA)) { // 公式
			cellvalue = String.valueOf(cell.getCellFormula());
		} else if (cell.getCellType().equals(CellType.BLANK) || cell.getCellType().equals(CellType.ERROR)) {
			cellvalue = "";
		}
		if (StringUtils.isEmpty(cellvalue)) {
			return null;
		}
		return cellvalue.trim();
	}

	public static void workbookDownload(Workbook workbook,HttpServletRequest request, HttpServletResponse response, 
	        String fileName) {
		response.reset();
		String downloadFileName = null;
		try {
			String agent = request.getHeader("user-agent");
			if (fileName != null && !fileName.trim().isEmpty()) {
				if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
					downloadFileName = "=?UTF-8?B?" + (getEncodedBase64(fileName)) + "?=";
				} else {
					downloadFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
				}
			}
			if (downloadFileName == null) {
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat(YYYY_MM_DD_HH_MM_SS, Locale.CHINA);
				downloadFileName = simpleDateFormat.format(new Date()) + ".xlsx";
			}
			// .xls -> application/vnd.ms-excel application/x-excel
			// .xlsx ->
			// application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
			response.setHeader("Content-Disposition", "attachment;filename=\"" + downloadFileName + "\"");
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
			response.setHeader("Pragma", "no-cache");
			response.setHeader("Cache-Control", "no-cache");
			response.setDateHeader("Expires", 0);
			OutputStream output = response.getOutputStream();
			workbook.write(output);
			output.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * base64加密方法
	 * 
	 * @param plainText
	 * @return
	 */

	public static String getEncodedBase64(String plainText) {
		String encoded = null;
		try {
			byte[] bytes = plainText.getBytes("UTF-8");
			encoded = Base64.getEncoder().encodeToString(bytes);
		} catch (UnsupportedEncodingException e) {
		}

		return encoded;
	}

	/**
	 * base64解密方法
	 *
	 * @param plainText
	 * @return
	 */
	public static byte[] getDecodedBase64(String plainText) {
		byte[] decoded = null;
		try {
			byte[] bytes = plainText.getBytes("UTF-8");
			decoded = Base64.getDecoder().decode(bytes);
		} catch (Exception e) {
		}

		return decoded;
	}

}
